﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Text;

/// <summary>
/// Summary description for CarColorDAL
/// </summary>
public class CarColorDAL
{
	public CarColorDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public static List<CarColor> GetAll()
    {
        string strSQL = @"SELECT [CarColorID]
                          ,[Name]
                          ,[Description]
                          ,C.[LastUser]
                          ,C.[LastUpdate]
                          ,E.FirstName
                          ,E.LastName
                      FROM [CarColor] C
                      LEFT JOIN   
                        Employee E ON E.EmployeeID=C.LastUser ORDER BY Name";
        SqlDataReader reader;
        List<CarColor> list = new List<CarColor>();
        CarColor cardcolor = null;
        try
        {
            reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL);
            if (reader.HasRows)
            {
                list = new List<CarColor>();
                while (reader.Read())
                {
                    cardcolor = new CarColor();
                    cardcolor.ID = (Guid)reader["CarColorID"];
                    cardcolor.Name = reader["Name"].ToString();
                    cardcolor.Description = reader["Description"].ToString();
                    Employee emp = new Employee();
                    emp.Id = SqlHelper.CheckGuidNull(reader["LastUser"]);
                    emp.FirstName = SqlHelper.CheckStringNull(reader["FirstName"]);
                    emp.LastName = SqlHelper.CheckStringNull(reader["LastName"]);
                    cardcolor.LastUser = emp;

                    list.Add(cardcolor);
                }
            }
            return list;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static string GetName(Guid id)
    {
        string strSQL = @"SELECT [Name]      
                          FROM [CarColor]
                          WHERE CarColorId=@CarColorId";

        //parms
        SqlParameter parm = new SqlParameter("@CarColorId", id);

        try
        {
            object obj=SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parm);
            if (obj == null) return String.Empty;
            return obj.ToString();
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static bool InsertOrUpdate(CarColor carColor)
    {

        #region Sql sentence
        string strSQLInsert = @"INSERT INTO [CarColor] (
	                                    [CarColorID],
	                                    [Name],
	                                    [Description],
	                                    [LastUser],
	                                    [LastUpdate]
                                    ) VALUES (
	                                    NewID(),
	                                    @Name,
	                                    @Description,
	                                    @LastUser,
	                                    GetDate()
                                    )";

        string strSQLUpdate = @"UPDATE [CarColor] SET
	                                    [Name] = @Name,
	                                    [Description] = @Description,
	                                    [LastUser] = @LastUser,
	                                    [LastUpdate] = GetDate()
                                    WHERE
	                                    [CarColorID] = @CarColorID";
        #endregion

        //parms
        SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@CarColorID",carColor.ID),
                        new SqlParameter("@Name",carColor.Name),
                        new SqlParameter("@Description",carColor.Description),
                        new SqlParameter("@LastUser",carColor.LastUser.Id),
                    };

        string strSQL;
        //Insert case
        if (carColor.ID == Guid.Empty)
        {
            strSQL = strSQLInsert;
        }
        //delete case
        else
        {
            strSQL = strSQLUpdate;
        }

        try
        {
            return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parms) > 0);
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static CarColor GetById(Guid id)
    {
        string strSQL = @"SELECT
	                            [CarColorID],
	                            [Name],
	                            [Description]
                            FROM
	                            [CarColor]
                            WHERE
	                            [CarColorID] = @CarColorID";
        SqlDataReader reader;
        CarColor cardColor = null;

        //parms
        SqlParameter parm = new SqlParameter("@CarColorID", id);


        try
        {
            reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL, parm);

            if (reader.HasRows)
            {
                reader.Read();
                cardColor = new CarColor();
                cardColor.ID = (Guid)reader["CarColorID"];
                cardColor.Name = reader["Name"].ToString();
                cardColor.Description = reader["Description"].ToString();
            }
            return cardColor;
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static bool Delete(Guid id)
    {
        string strSQL = "DELETE FROM CarColor WHERE CarColorID=@CarColorID";

        //parms
        SqlParameter parm = new SqlParameter("@CarColorID", id);
        try
        {
            return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parm) > 0);
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static bool IsExistCardType(string name)
    {
        #region Sql sentence
        string strSQL = @"Select count(CarColorID) from CarColor WHERE Name=@Name";

        #endregion

        //parms
        SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@Name",name)
                    };
        try
        {
            return Int16.Parse(SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parms).ToString()) > 0;
        }
        catch (Exception)
        {
            throw;
        }
    } 
}